﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Tasks.ScriptTask;
using Microsoft.SqlServer.VSTAHosting;
using System.IO;

namespace SSISCipherBoy
{
    class SSISPackageEditor
    {
        List<string> onPreExecuteAllPrecedenceExecutablesNames;
        List<string> onPreExecuteAllConstrainedExecutablesNames;
        List<string> onPreExecuteTopMostPreceedenceExecutableNames;
        List<string> onPreExecuteBottomMostPreceedenceExecutableNames;
        List<string> onPreExecuteAllExecutablesNames;
        List<string> onPreExecuteDanglingExecutablesNames;

        Microsoft.SqlServer.Dts.Runtime.Application ssisApplication = new Microsoft.SqlServer.Dts.Runtime.Application();
        Package ssisPackage = default(Package);
        DtsEventHandler onPreExecute = default(DtsEventHandler);
        Executable seqDecryptionExec = default(Executable);
        Sequence seqDecryption = default(Sequence);
        string targetEventName = "OnPostExecute";
        int onPreExecuteAllExecCount;
        int onPreExecuteDanglingExecCount;
        int onPreExecutePreConstraintsCount;
        List<Executable> constrainedExecutablesFollowingDecryptionSequence = default(List<Executable>);

        private void LoadPackageFromApplication(string absolutePackagePath, string packagePassword)
        {
            try
            {
                if (!string.IsNullOrEmpty(packagePassword))
                {
                    ssisApplication.PackagePassword = packagePassword;
                    ssisPackage = ssisApplication.LoadPackage(absolutePackagePath, null);
                }
                else
                    ssisPackage = ssisApplication.LoadPackage(absolutePackagePath, null);
            }
            catch(DtsRuntimeException dtxREx)
            {
                if (dtxREx.ErrorCode == -1073659849 & dtxREx.Message == "Failed to remove package protection with error 0xC0014037 \"The package is encrypted with a password. The password was not specified, or is not correct.\". This occurs in the CPackage::LoadFromXML method.\r\n")
                {
                    throw new SSISCipherUtil.PasswordNotProvidedException();
                }
            }
            catch(DtsComException)
            {

            }
            catch(Exception)
            {

            }

            //if package loaded succesfully, check for protection level
            DTSProtectionLevel protectionLevel = ssisPackage.ProtectionLevel;
            if (protectionLevel == DTSProtectionLevel.EncryptSensitiveWithPassword)
            {
                if(!string.IsNullOrEmpty(packagePassword))
                    ssisPackage.PackagePassword = packagePassword;
                else
                    throw new SSISCipherUtil.PasswordNotProvidedException();
            }
        }

        private void ObtainEventHandler(string pEventName)
        {
            foreach (DtsEventHandler eventHandler in ssisPackage.EventHandlers)
            {
                if (eventHandler.CreationName == pEventName)
                {
                    onPreExecute = ssisPackage.EventHandlers[pEventName];
                }
            }

            if (onPreExecute == default(DtsEventHandler))
            {
                onPreExecute = (DtsEventHandler)ssisPackage.EventHandlers.Add(pEventName);
            }
        }

        private void ObtainDecryptionSequence(ref string scriptMainDotCsCode, string referenceAssemblyName)
        {
            Executable stDummyExec = default(Executable);
            Executable stDecryptExec = default(Executable);

            if (!onPreExecuteAllExecutablesNames.Contains("SSISCipherUtil_seqDecryption"))
            {
                seqDecryptionExec = onPreExecute.Executables.Add("STOCK:SEQUENCE");
                seqDecryption = (Sequence)seqDecryptionExec;
                seqDecryption.Name = "SSISCipherUtil_seqDecryption";
                seqDecryption.Description = "AUTOGENERATED_DO_NOT_MODIFY_SISCipherUtil_seqDecryption_sequence_containing_decrypion_scripttask";


                stDummyExec = seqDecryption.Executables.Add("STOCK:ScriptTask");
                TaskHost stDummyTaskHost = (TaskHost)stDummyExec;
                stDummyTaskHost.Properties["Name"].SetValue(stDummyTaskHost, "SSISCipherUtil_stDummyForPkgOnlyOnPreExecuteAllow");
                stDummyTaskHost.Properties["Description"].SetValue(stDummyTaskHost, "AUTOGENERATED_DO_NOT_MODIFY_SSISCipherUtil_stDummyForPkgOnlyOnPreExecuteAllow_scripttask_to_allow_decryption_only_for_package_level_onpreexecuteevent");

                stDecryptExec = seqDecryption.Executables.Add("STOCK:ScriptTask");
                TaskHost stDecryptTaskHost = (TaskHost)stDecryptExec;
                stDecryptTaskHost.Properties["Name"].SetValue(stDecryptTaskHost, "SSISCipherUtil_stDecryptConfigValues");
                stDecryptTaskHost.Properties["Description"].SetValue(stDecryptTaskHost, "AUTOGENERATED_DO_NOT_MODIFY_SSISCipherUtil_stDecryptConfigValues_scripttask_to_decrypt_configuration_values");

                ScriptTask stDecrypt = (ScriptTask)stDecryptTaskHost.InnerObject;

                EditScriptTaskCode(ref stDecrypt, ref scriptMainDotCsCode, referenceAssemblyName);
            }
            //modify the existing sequence
            else
            {
                seqDecryptionExec = onPreExecute.Executables["SSISCipherUtil_seqDecryption"];
                seqDecryption = (Sequence)seqDecryptionExec;

                if (seqDecryption.Executables.Contains("SSISCipherUtil_stDummyForPkgOnlyOnPreExecuteAllow"))
                    seqDecryption.Executables.Remove("SSISCipherUtil_stDummyForPkgOnlyOnPreExecuteAllow");
                if (seqDecryption.Executables.Contains("SSISCipherUtil_stDecryptConfigValues"))
                    seqDecryption.Executables.Remove("SSISCipherUtil_stDecryptConfigValues");

                List<string> precedenceConstraintsToRemove = new List<string>();
                foreach (PrecedenceConstraint pc in seqDecryption.PrecedenceConstraints)
                {
                    precedenceConstraintsToRemove.Add(pc.Name);
                }

                foreach (string s in precedenceConstraintsToRemove)
                {
                    seqDecryption.PrecedenceConstraints.Remove(s);
                }

                if (onPreExecuteTopMostPreceedenceExecutableNames.Contains("SSISCipherUtil_seqDecryption"))
                    onPreExecuteTopMostPreceedenceExecutableNames.Remove("SSISCipherUtil_seqDecryption");
                if (onPreExecuteDanglingExecutablesNames.Contains("SSISCipherUtil_seqDecryption"))
                    onPreExecuteDanglingExecutablesNames.Remove("SSISCipherUtil_seqDecryption");
                if (onPreExecuteAllExecutablesNames.Contains("SSISCipherUtil_seqDecryption"))
                    onPreExecuteAllExecutablesNames.Remove("SSISCipherUtil_seqDecryption");



                stDummyExec = seqDecryption.Executables.Add("STOCK:ScriptTask");
                TaskHost stDummyTaskHost = (TaskHost)stDummyExec;
                stDummyTaskHost.Properties["Name"].SetValue(stDummyTaskHost, "SSISCipherUtil_stDummyForPkgOnlyOnPreExecuteAllow");
                stDummyTaskHost.Properties["Description"].SetValue(stDummyTaskHost, "AUTOGENERATED_DO_NOT_MODIFY_SSISCipherUtil_stDummyForPkgOnlyOnPreExecuteAllow_scripttask_to_allow_decryption_only_for_package_level_onpreexecuteevent");

                stDecryptExec = seqDecryption.Executables.Add("STOCK:ScriptTask");
                TaskHost stDecryptTaskHost = (TaskHost)stDecryptExec;
                stDecryptTaskHost.Properties["Name"].SetValue(stDecryptTaskHost, "SSISCipherUtil_stDecryptConfigValues");
                stDecryptTaskHost.Properties["Description"].SetValue(stDecryptTaskHost, "AUTOGENERATED_DO_NOT_MODIFY_SSISCipherUtil_stDecryptConfigValues_scripttask_to_decrypt_configuration_values");

                ScriptTask stDecrypt = (ScriptTask)stDecryptTaskHost.InnerObject;

                EditScriptTaskCode(ref stDecrypt, ref scriptMainDotCsCode, referenceAssemblyName);

            }


            //Add PrecedenceConstraint between Dummy script task and decrypt script task
            PrecedenceConstraint respondOnlyForPackageEvents = seqDecryption.PrecedenceConstraints.Add(stDummyExec, stDecryptExec);
            respondOnlyForPackageEvents.Expression = "@[System::SourceID]== @[System::PackageID]";
            respondOnlyForPackageEvents.EvalOp = DTSPrecedenceEvalOp.ExpressionAndConstraint;
            respondOnlyForPackageEvents.Value = DTSExecResult.Completion;
            respondOnlyForPackageEvents.LogicalAnd = true;
        }

        private bool EditScriptTaskCode(ref ScriptTask pScriptTask, ref string scriptMainDotCsCode, string referenceAssemblyName)
        {
            pScriptTask.ScriptProjectName = "stSSISCipherUtil_DecryptConfigValues";
            pScriptTask.ScriptLanguage = VSTAScriptLanguages.GetDisplayName("CSharp");
            pScriptTask.ScriptingEngine.InitNewScript("CSharp", pScriptTask.ScriptProjectName, ".csproj");
            pScriptTask.ScriptingEngine.ShowDesigner(false);
            pScriptTask.ScriptingEngine.AddProjectReference(referenceAssemblyName);
            try
            {
                pScriptTask.ScriptingEngine.AddCodeFile("ScriptMain.cs", scriptMainDotCsCode);
            }
            catch (Exception)
            {
                //try one more time
                pScriptTask.ScriptingEngine.CloseIDE(true);
                //try one more time
                pScriptTask.ScriptProjectName = "stSSISCipherUtil_DecryptConfigValues";
                pScriptTask.ScriptLanguage = VSTAScriptLanguages.GetDisplayName("CSharp");
                pScriptTask.ScriptingEngine.InitNewScript("CSharp", pScriptTask.ScriptProjectName, ".csproj");
                pScriptTask.ScriptingEngine.ShowDesigner(false);
                pScriptTask.ScriptingEngine.AddProjectReference(referenceAssemblyName);
                pScriptTask.ScriptingEngine.AddCodeFile("ScriptMain.cs", scriptMainDotCsCode);
            }

            if (!pScriptTask.ScriptingEngine.Build())
                return false;

            pScriptTask.ScriptingEngine.SaveScriptToStorage();
            pScriptTask.ScriptingEngine.CloseIDE(false);
            return true;
        }

        private void AttachPrecedenceToDecryptionSequence3(Executable sequenceExec)
        {
            constrainedExecutablesFollowingDecryptionSequence = new List<Executable>();

            //if no other tasks are there in onPreExecute event handler, just return
            if (onPreExecuteAllExecCount == 0)
            {
                //decrypt sequence is the only task, so do nothing
            }
            else if (onPreExecuteDanglingExecCount == 0)
            {
                foreach (string execName in onPreExecuteTopMostPreceedenceExecutableNames)
                {
                    constrainedExecutablesFollowingDecryptionSequence.Add(onPreExecute.Executables[execName]);
                }
            }
            else if (onPreExecuteDanglingExecCount > 0 && onPreExecutePreConstraintsCount == 0)
            {
                foreach (string execName in onPreExecuteAllExecutablesNames)
                {
                    constrainedExecutablesFollowingDecryptionSequence.Add(onPreExecute.Executables[execName]);
                }
            }
            else if (onPreExecuteDanglingExecCount > 0 && onPreExecutePreConstraintsCount > 0)
            {
                foreach (string execName in onPreExecuteTopMostPreceedenceExecutableNames)
                {
                    constrainedExecutablesFollowingDecryptionSequence.Add(onPreExecute.Executables[execName]);
                }
                foreach (string execName in onPreExecuteDanglingExecutablesNames)
                {
                    constrainedExecutablesFollowingDecryptionSequence.Add(onPreExecute.Executables[execName]);
                }
            }

            foreach (Executable constrainedExecutableFollowingDecryptionSequence in constrainedExecutablesFollowingDecryptionSequence)
            {
                PrecedenceConstraint topMost = onPreExecute.PrecedenceConstraints.Add(sequenceExec, constrainedExecutableFollowingDecryptionSequence);
                topMost.LogicalAnd = true;
                topMost.Value = DTSExecResult.Completion;
                topMost.EvalOp = DTSPrecedenceEvalOp.Constraint;
            }

        }

        public void MakePackageReadyForDecryption(string absolutePackagePath, ref string scriptMainDotCsCode, string referenceAssemblyName, bool overwrite, string packagePassword)
        {
            LoadPackageFromApplication(absolutePackagePath, packagePassword);
            ObtainEventHandler("OnPreExecute");
            GetExecutableStatistics(onPreExecute);
            ObtainDecryptionSequence(ref scriptMainDotCsCode, referenceAssemblyName);
            AttachPrecedenceToDecryptionSequence3(seqDecryptionExec);
            SetDelayValidationToFalse();
            SaveWithLayoutWithWorkAround(absolutePackagePath, overwrite, packagePassword);
        }

        private void GetExecutableStatistics(IDTSSequence input)
        {
            //CreationName has STOCK:SEQUENCE
            //Name has sequencename
            onPreExecuteAllPrecedenceExecutablesNames = new List<string>();
            onPreExecuteAllConstrainedExecutablesNames = new List<string>();
            foreach (PrecedenceConstraint pc in input.PrecedenceConstraints)
            {
                onPreExecuteAllPrecedenceExecutablesNames.Add(((DtsContainer)(pc.PrecedenceExecutable)).Name);
                onPreExecuteAllConstrainedExecutablesNames.Add(((DtsContainer)(pc.ConstrainedExecutable)).Name);
            }

            //compare the two lists. if the item occurance is not more than one, then it is the entry in PrecedenceExecutableList
            // and the exit in the ConstrainedExecutableList

            onPreExecuteTopMostPreceedenceExecutableNames = new List<string>();
            foreach (string s in onPreExecuteAllPrecedenceExecutablesNames)
            {
                if (onPreExecuteAllConstrainedExecutablesNames.Contains(s))
                {
                    //connected node
                }
                else
                {
                    if (!onPreExecuteTopMostPreceedenceExecutableNames.Contains(s))
                    {
                        onPreExecuteTopMostPreceedenceExecutableNames.Add(s);
                    }
                }
            }

            onPreExecuteBottomMostPreceedenceExecutableNames = new List<string>();
            foreach (string s in onPreExecuteAllConstrainedExecutablesNames)
            {
                if (onPreExecuteAllPrecedenceExecutablesNames.Contains(s))
                {
                    //conected node
                }
                else
                {
                    if (!onPreExecuteBottomMostPreceedenceExecutableNames.Contains(s))
                    {
                        onPreExecuteBottomMostPreceedenceExecutableNames.Add(s);
                    }
                }
            }

            onPreExecuteAllExecutablesNames = new List<string>();
            onPreExecuteDanglingExecutablesNames = new List<string>();
            foreach (Executable executable in input.Executables)
            {
                string execName = ((DtsContainer)executable).Name;
                onPreExecuteAllExecutablesNames.Add(execName);

                if (!onPreExecuteAllPrecedenceExecutablesNames.Contains(execName) && !onPreExecuteAllConstrainedExecutablesNames.Contains(execName))
                {
                    onPreExecuteDanglingExecutablesNames.Add(execName);
                }
            }

            onPreExecuteAllExecCount = input.Executables.Count;
            onPreExecuteDanglingExecCount = onPreExecuteDanglingExecutablesNames.Count;
            onPreExecutePreConstraintsCount = input.PrecedenceConstraints.Count;
        }

        /// <summary>
        /// Fix incorporated as suggessted in http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/974c43ae-0e2f-48a5-a75c-cb2ddbbdb425
        /// </summary>
        /// <param name="absolutePackagePath"></param>
        /// <param name="overwrite"></param>
        /// <param name="packagePassword"></param>
        private void SaveWithLayoutWithWorkAround(string absolutePackagePath, bool overwrite, string packagePassword)
        {
            string strXML = default(string);
            ssisPackage.SaveToXML(out strXML, null);
            //remove all the layout information
            while (strXML.IndexOf("<DTS:PackageVariable>") > -1)
            {
                strXML = strXML.Remove(strXML.IndexOf("<DTS:PackageVariable>"), strXML.IndexOf("</DTS:PackageVariable>") - strXML.IndexOf("<DTS:PackageVariable>") + 22);
            }
            Package pkg2 = new Package();
            if(!string.IsNullOrEmpty(packagePassword))
                pkg2.PackagePassword = packagePassword;

            pkg2.LoadFromXML(strXML, null);
            string tempName = absolutePackagePath;
            if (!overwrite)
            {
                tempName = tempName.Insert(tempName.LastIndexOf(".dtsx", StringComparison.InvariantCultureIgnoreCase), "-Mod");
            }
            ssisApplication.SaveToXml(tempName, pkg2, null);
        }

        private void SetDelayValidationToFalse()
        {
            ssisPackage.DelayValidation = true;
            foreach (ConnectionManager cm in ssisPackage.Connections)
            {
                cm.DelayValidation = true;
            }
            //disable configurations
            ssisPackage.EnableConfigurations = false;
        }
    }
}
